﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using Lead2012.Models;
using Lead2012.ViewModels;
using Lead2012.Helpers;
using Lead2012.Business;

namespace Lead2012.Repository
{
    public class CalendarItemManagementRepository : BaseManagementRepository
    {
        // Create an instance of entities framework
        Lead2012Entities entities = new Lead2012Entities();

        #region Build Models
        public void GetModel(ref CalendarItemViewModel model)
        {
            model.UserFilter = GetAllUsers();
            model.UserFilter.Add(new ItemFilter() { ID = "0", ItemName = "Tất cả" });
            model.ItemList = GetAllCalendarItems(ref model);
        }
        #endregion

        #region Private methods
        private List<CalendarEventItem> GetAllCalendarItems(ref CalendarItemViewModel model)
        {
            List<CalendarEventItem> list = new List<CalendarEventItem>();

            // FIRST: Get all field system mapping with query
            //      To assign for Name, Phone, Email
            var listFieldSystem = new FieldSystemRepository().GetAll().Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).ToList();

            // SECOND: Create a query string
            string sql = BuildParamsQuery(listFieldSystem, int.Parse(model.UserId), model.FromDate, model.ToDate);

            // THIRD: Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, sql))
            {
                while (item.Read())
                {
                    CalendarEventItem cal = new CalendarEventItem();

                    cal.ItemId = item.GetInt32(0);
                    cal.Title = item.GetString(1);
                    cal.EventDate = item.GetDateTime(2).ToString("dd-MM-yyyy");
                    cal.EventTime = item.GetDateTime(2).ToString("hh:mm tt");
                    cal.DurationMins = item.GetInt32(3);
                    cal.CreationDate = item.GetDateTime(4).ToString("dd-MM-yyyy hh:mm tt");
                    cal.LeadId = item.GetInt32(5);
                    cal.LeadName = item.GetString(6);

                    list.Add(cal);
                }
            }

            if (list != null && list.Count > 0)
            {
                model.TotalRecord = list.Count;
                model.TotalPage = (list.Count % model.PageSize == 0) ? (list.Count / model.PageSize) : (list.Count / model.PageSize + 1);
            }

            return list.Skip((model.PageIndex - 1) * model.PageSize).Take(model.PageSize).ToList(); ;
        }
        
        private string BuildParamsQuery(List<FieldSystem> listFieldSystem, int userId, DateTime startDate, DateTime endDate)
        {
            string sqlQuery = @"SELECT CalendarItem.ItemId
                        , CalendarItem.Title
                        , CalendarItem.StartDate
                        , CalendarItem.DurationMins
                        , CalendarItem.CreateDate
                        , CalendarItem.LeadId as LeadId
                        , ({0}) as LeadName
                        FROM CalendarItem INNER JOIN
                            LeadWarehouse ON CalendarItem.LeadId = LeadWarehouse.LeadId
                        WHERE {1}
                        ORDER BY [ItemId] desc";

            string paramName = string.Empty;
            if (listFieldSystem != null && listFieldSystem.Count > 0)
            {
                int i = 0;
                // Build Name
                foreach (var item in listFieldSystem)
                {
                    i++;
                    if (i == 1)
                        paramName += string.Format("f{0}", item.FieldId);
                    else
                        paramName += string.Format("+f{0}", item.FieldId);
                }

            }

            string where = string.Empty;
            if (userId > 0)
            {
                where = string.Format(" CalendarItem.UserId={0} AND (CalendarItem.CreateDate Between '{1}' AND '{2}')", userId, startDate, endDate);
            }
            else
            {
                where = string.Format(" CalendarItem.ItemId > 0 AND (CalendarItem.CreateDate Between '{0}' AND '{1}')", startDate, endDate);
            }
            sqlQuery = string.Format(sqlQuery, paramName, where);

            return sqlQuery;
        }
        #endregion
    }
}